package cn.mrlong.tools.bean

import java.sql.Connection

/**
 * @author LeeWyatt
 */
class NoteDaoImpl() : BaseDAO<Note?>(), NoteDao {


    override fun insert(conn: Connection?, notes: Array<Note?>?) {
        notes?.let {

        }
        if (notes == null || notes.isEmpty()) {
            return
        }
        val sql = "insert into note(show_order,title,chapter_id,notebook_id,create_time,update_time,content,description,source,type,offset_start,offset_end,image_records) values(ifnull((select seq from sqlite_sequence where name='note'),0)+1,?,?,?,?,?,ifnull(?,''),ifnull(?,''),ifnull(?,''),ifnull(?,''),?,?,?)"
        val size = notes.size
        if (size == 1) {
            val note = notes[0]!!
            update(conn, sql, note.title, note.chapterId, note.notebookId, note.createTime, note.updateTime, note.content, note.description, note.source, note.type, note.offsetStart, note.offsetEnd, note.imageRecords)
        } else {
            val args = Array<Array<Any?>?>(size) { arrayOfNulls(12) }
            for (i in 0 until size) {
                val note = notes[i]!!
                args[i]!![0] = note.title
                args[i]!![1] = note.chapterId
                args[i]!![2] = note.notebookId
                args[i]!![3] = note.createTime
                args[i]!![4] = note.updateTime
                args[i]!![5] = note.content
                args[i]!![6] = note.description
                args[i]!![7] = note.source
                args[i]!![8] = note.type
                args[i]!![9] = note.offsetStart
                args[i]!![10] = note.offsetEnd
                args[i]!![11] = note.imageRecords
            }
            updateBatch(conn, sql, args)
        }
    }

    override fun insert(conn: Connection?, note: Note?): Note? {
        if (note == null) return null
        val sql = "insert into note(show_order,title,chapter_id,notebook_id,create_time,update_time,content,description,source,type,offset_start,offset_end,image_records) values(ifnull((select seq from sqlite_sequence where name='note'),0)+1,?,?,?,?,?,ifnull(?,''),ifnull(?,''),ifnull(?,''),ifnull(?,''),?,?,?); "
        update(conn, sql, note.title, note.chapterId, note.notebookId, note.createTime, note.updateTime, note.content, note.description, note.source, note.type, note.offsetStart, note.offsetEnd, note.imageRecords)
        val sqlSelect = SELECT_SQL + " where id=(select LAST_INSERT_ROWID()) limit 1;"
        return getBean(conn, sqlSelect)
    }

    override fun delete(conn: Connection?, id: Int?) {
        val sql = "delete from note where id =?"
        update(conn, sql, id)
    }

    override fun update(conn: Connection?, notes: Array<Note?>?) {
        if (notes == null || notes.size == 0) {
            return
        }
        val sql = "update note set title=? ,show_order=?,chapter_id=?,notebook_id=?,create_time=?,update_time=?,content=?,description=?,source=?,type=?,offset_start=?,offset_end=?,image_records=? where id =?"
        val size = notes.size
        if (size == 1) {
            val note = notes[0]!!
            update(conn, sql, note.title, note.showOrder, note.chapterId, note.notebookId, note.createTime, note.updateTime, note.content, note.description, note.source, note.type, note.offsetStart, note.offsetEnd, note.imageRecords, note.id)
        } else {
            val args = Array<Array<Any?>?>(size) { arrayOfNulls(14) }
            for (i in 0 until size) {
                val note = notes[i]!!
                args[i]!![0] = note.title
                args[i]!![1] = note.showOrder
                args[i]!![2] = note.chapterId
                args[i]!![3] = note.notebookId
                args[i]!![4] = note.createTime
                args[i]!![5] = note.updateTime
                args[i]!![6] = note.content
                args[i]!![7] = note.description
                args[i]!![8] = note.source
                args[i]!![9] = note.type
                args[i]!![10] = note.offsetStart
                args[i]!![11] = note.offsetEnd
                args[i]!![12] = note.imageRecords
                args[i]!![13] = note.id
            }
            updateBatch(conn, sql, args)
        }
    }

    override fun update(conn: Connection?, note: Note?) {
        if (note == null) return
        val sql = "update note set title=?,show_order=?,chapter_id=?,notebook_id=?,create_time=?,update_time=?,content=?,description=?,source=?,type=?,offset_start=?,offset_end=?,image_records=? where id =?"
        update(conn, sql, note.title, note.showOrder, note.chapterId, note.notebookId, note.createTime, note.updateTime, note.content, note.description, note.source, note.type, note.offsetStart, note.offsetEnd, note.imageRecords, note.id)
    }

    override fun findById(conn: Connection?, id: Int?): Note? {
        val sql = SELECT_SQL + "where id =? limit 1"
        return getBean(conn, sql, id)
    }

    override fun exchangeShowOrder(conn: Connection?, showOrder1: Int?, showOrder2: Int?) {
        val sql = "UPDATE note " +
                "SET show_order = (CASE WHEN show_order = ? THEN ? " +
                "                  WHEN show_order = ?  THEN ? END )" +
                "where show_order=? or show_order=?"
        update(conn, sql, showOrder1, showOrder2, showOrder2, showOrder1, showOrder1, showOrder2)
    }

    override fun findAllByChapterId(conn: Connection?, chapterId: Int?): List<Note?>? {
        val sql = SELECT_SQL + "where chapter_id=? order by show_order desc"
        return queryList(conn, sql, chapterId)
    }
    fun findAllByKeyWords(conn: Connection?, key: String?): List<Note?>? {
        val sql = SELECT_SQL + "where title like '%" + key + "%' order by id desc"
        print(sql)
        return queryList(conn, sql)
    }
    fun findAll(conn: Connection?): List<Note?>? {
        val sql = "$SELECT_SQL order by id desc"
        print(sql)
        return queryList(conn, sql)
    }

    override fun deleteAllByChapterId(conn: Connection?, chapterId: Int?) {
        val sql = "delete from note where chapter_id =?"
        update(conn, sql, chapterId)
    }

    override fun deleteAllByNotebookId(conn: Connection?, notebookId: Int?) {
        val sql = "delete from note where notebook_id =?"
        update(conn, sql, notebookId)
    }

    override fun getTitles(conn: Connection?, notebookTitle: String?, chapterTitle: String?): List<String?>? {
        val sql = "select distinct title from note " +
                "where notebook_id=(select id from notebook where title=?) " +
                "and " +
                "chapter_id=(select id from chapter where title=? and notebook_id=(select id from notebook where title=?))  order by show_order desc;"
        return queryTitleList(conn, sql, notebookTitle, chapterTitle, notebookTitle)
    }

    override fun findByTitle(conn: Connection?, noteName: String?, chapterId: Int?): Note? {
        val sql = SELECT_SQL + " where chapter_id=? and title =? limit 1"
        return getBean(conn, sql, chapterId, noteName)
    }

    fun findNote(conn: Connection?, noteName: String?, chapterId: Int?): Note? {
        val sql = SELECT_SQL + " where chapter_id=? and title =? limit 1"
        return getBean(conn, sql, chapterId, noteName)
    }

    override fun findByTitles(conn: Connection?, noteTile: String?, chapterTitle: String?, notebookTitle: String?): Note? {
        val sql = SELECT_SQL +
                " where notebook_id=(select id from notebook where title=?) " +
                "and " +
                "chapter_id=(select id from chapter where title=? and notebook_id=(select id from notebook where title=?)) " +
                "and " +
                "title =? limit 1"
        return getBean(conn, sql, notebookTitle, chapterTitle, notebookTitle, noteTile)
    }

    override fun getImageRecordsByNotebookId(conn: Connection?, notebookId: Int): List<String?>? {
        val sql = "select image_records from note where notebook_id=?"
        return queryImageRecords(conn, sql, notebookId)
    }

    override fun getImageRecordsByChapterId(conn: Connection?, chapterId: Int): List<String?>? {
        val sql = "select image_records from note where chapter_id=?"
        return queryImageRecords(conn, sql, chapterId)
    }

    override fun getImageRecordsByNoteId(conn: Connection?, noteId: Int): List<String?>? {
        val sql = "select image_records from note where id=?"
        return queryImageRecords(conn, sql, noteId)
    }


    companion object {
        @get:Synchronized
        public var instance: NoteDaoImpl? = null
            get() {
                if (field == null) {
                    field = NoteDaoImpl()
                }
                return field
            }
            private set

        private const val SELECT_SQL = "select id,title,chapter_id,notebook_id,create_time,update_time,show_order,content,description,source,type,offset_start,offset_end,image_records from note "
    }
}
